symbol. The match is between the the first field (left side of arrow) and the key of the looked up file as defined by the size of key and location of key at Define Files and Fields. The key field does not print on the report.
The field on the right side of the symbol is the field that you actually want to print on your report. It does not have to be the key field. It can be any field in the looked-up file.
The [n] is to be replaced by the key number, as defined on the first screen of Define Files and Fields. The primary key is [1]. Alternate keys are from [2] through [6]. If you enter a [0], [1] or no number, then the program will default to the primary key field.
Example: account customer no -> customer name.key[1]
In the above example, the customer no in the accounts file matches up with the customer number (the primary key) of the customer file. Then the customer names are found and printed.
* Section *
18.2.2 File Relationships Used for Keyed-Read Lookup
The keyed read look-up can be used with either of the two file relationships shown below. Also note that a keyed read look-up can be done within a file, as outlined in the immediately following section.
| commissions salesman no---------------->salesman no |
| commissions customer no | | salesman name |
| commissions document no | |________________________________|
| commissions document type | | commissions document date |
| commissions amount due |
|_________________________________|
(Many commission records for each salesman)
Example: commissions salesman no -> salesman name
* Section *
18.2.3 Performing a Look-up Within One File
It is possible to perform a look-up from one type of record in a file to another type of record in that same file. This may be necessary if you have one master file which includes all of your data, regardless of data type (e.g. accounts receivable, customer data, transactions, etc.) In this type of file, you would most likely have a field which identifies the type of record. This field may be something like record type, and possible values could be CUS for customer records, GLA for general ledger accounts, etc. Then each different record type would have its own fields and field sizes (not necessarily the same as other record types). Because of these varying field types and locations, you would need to specify a different file definition for each of these types of records, and the fields related to that record type.
The reason why it is necessary to perform a look-up even though all of these fields are in the same file, is that for use with Report Writer, these are different 'files'. You could not simply define the file as one large file, because the different fields in the different records types usually have different starting and ending locations and field types.
When defining the different 'files', use the following guidelines:
The Data File Name can be whatever you want to call that record type.
The Name of File on Disk would be the same on each of these definitions. This would be the actual name of the file on disk.
The Size of Record may vary on each of these file definitions.
The Primary and Alternate Keys (size and location of) may also vary from one record type to the next.
The Field Separator Value and Location of Delete Flag can usually be left blank, unless the record has a delete flag which you want to use.
The Start of Data Location and End of Data Location may vary for each of these file definitions. These two selections can be used to define the beginning and end of each section of the master file. Refer to Chapter 11 of this Reference Manual, Defining Files and Fields, at Start of Data Location for details. (Alternatively, use a Selection Criteria to split the file, as described below.)
It is suggested that you enter Y at Keep Index Permanent.
After defining each type of record as a file, then you must define any fields of each of the respective record types that you want to access with Report Writer. Define a record type's fields immediately after defining that record type as a file (second screen of Define Files and Fields). Use the standard field definition guidelines when defining the fields. Be sure to use a different field name for each field defined (even from one record type to the next). You may want to include the record type (or portion thereof) as part of the field name, to help differentiate different fields.
You must also define the field which contains the record type. These must also have different field names, one for each record type (e.g. cus record type, transaction record type, etc.)
Once these files and fields are defined, you can perform a look-up from one record type to another record type, within that same file. The lookup field must still match the key field of file (record type) being looked up. For example, the customer account numbers in the customer records can match the account numbers in the transaction records. So you could use the customer number to look up transaction records data.
Example: customer no -> transaction no
When performing the Query/Report, you must specify selection criteria to include only those records which are being used as your 'primary' data file. For example, if the customer-type records are being used as your primary data file, then your selection criteria would say something like this:
Example: record type = "CUS"
(OR Use Start of Data Loc/End of Data described above.)
* Section *
18.2.4 Concatenated Key Fields
When performing a keyed read look-up, if the data which matches the key field of the file being looked up contains more than one field, you can combine these fields when specifying a lookup. This creates a concatenated field. It must be enclosed in parentheses.
The key field of the looked-up file (File 2), the tax code, is a combination of both the record type and the code in File 1 (although the actual field name will vary from File 1's field names).
The file definition of File 2, containing the tax code, specifies the size of key as being the full length of the fields containing the record types and codes. The start location of the key is the start location of the record type field.
* Section *
18.2.5 Using Literals in a Concatenated Key
You may have a situation where you don't have a field in the first file which matches part of the key of the looked-up file. If this key is a constant in the looked-up file, then you can use a literal to help create the concatenated lookup field in the primary data file which will match the key to the looked-up file. The literal would then be part of the field.
In the above example, the code type is one of the following:
X = tax code
S = shipping code
T = terms code
For the look-up specification, the X's (indicating the codes are tax codes) are combined with the customer tax code, to match the key of the file which contains the codes descriptions.
* Section *
18.3 One-to-Many Keyed Read
This type of look-up is similar to the keyed read look-up, except that the looked-up file may contain many records per each record of the primary file (thus, one-to-many).
One-to-many look-up specifications will automatically create a level break. Therefore, do not specify a level break manually when specifying a one-to-many look-up specification.
Also note, when using subtotal levels and line types on your design, the level break created by a one-to-many look-up counts as a level break, even though not actually selected through the windows.
If additional level breaks are needed, they can be added before (but not after) a one-to-many look-up specification.
* Section *
18.3.1 One to Many Keyed Read Specification
The one-to-many lookup is also done through the use of a key field. Following is the format for a One-to-Many Keyed Read lookup specification. Note that the symbol is now +>.
Lookup Field +> Looked up Field .key[n]
(Matches key field (Field to include (Number of key
of looked up file) in Report being used)
The field which matches the key field is on the left side of the symbol. The match is between the the first field (left side of arrow) and the key of the looked up file as defined by the size of key and location of key at Define Files to be Queried. The key field does not print on the report.
The field on the right side of the symbol is the field that you actually want to print on your report. It does not have to be the key field. It can be any field in the looked-up file.
The [n] is to be replaced by the key number, as defined on the Define Files to be Queried screen. The primary key is [1]. Alternate keys are from [2] through [6]. If you enter a [0], [1] or no number, then the program will default to the primary key field.
Example: customer account no +> invoice no.key[2]
In the above example, the account number in the customer file matches up with the customer number (the second key) of the invoices file. Then the invoice numbers are found and printed.
It is recommended that you specify the key field first (separately). Otherwise you will get the second file's detail lines only. It is also recommended that you sort the report by this key field.
In the preceding example, you have one customer in
CUSFIL.DAT to many aropen documents in AROPEN.DAT.
The sort field should be customer no. An automatic level
break will be created for each new customer number.
Example: customer no +> aropen document no.key[1]
The above specification will give you all of the aropen document
numbers (from AROPEN.DAT) for each of the customers. The
AROPEN document numbers will be sorted by customer number.
* Section *
18.3.2 Concatenated Key Fields
If the key to the file being looked up contains more than one field, you can also combine the matching fields of the first file when specifying the look-up. This creates a concatenated key. This type of key must be enclosed in parentheses.
The key to File 2 is a combination of both the customer state and zip code. In the above example, the report would print all customers sorted by customer state.
In the above example, the file definition of the looked-up file (containing the field named location) specifies the size of key as being the full length of the fields containing the states and zip codes. The start location of key in record is the first byte position of the field named location in File 2.
* Section *
18.3.3 Using Literals in a Concatenated Key
You may have a situation where you don't have a field in the first file (report sequence file) which matches part of the key of the looked-up file. If this key is a constant in the looked up file, then you can use a literal to help create the lookup field in the report sequence file which will match the key of the looked up file.
The key of the looked up file (the file containing the field code) is a combination of both the record type and the state of the first file. In the above example, the report would print all customers from CUSFIL.DAT whose have the code of XFLA.
In the above example, the code type is one of the following:
X = tax code
S = shipping code
T = terms code
For the look-up specification, the X's (indicating the codes are tax codes) are combined with the customer tax code, to match the key to the file which contains the codes descriptions:
X , FLA = XFLA (Key to Codes description)
* Section *
18.3.4 Two One-to-Many Look-ups in One Report
It is possible to have more than one one-to-many look-up specification within the same report. The layout of the report will depend on which files are used for the second one-to-many specification.
Field 1 +> Field 2, ... Field 3 +> Field 4
In the above example, field 1 and field 3 can be in different files. Here is an example:
In the above look-up, the Customer No (customer.dat) is matched with the Customer Account No (account.dat). The file key is located through the location of key in record and size of key which was defined at Define Files and Fields. Then the Invoice Dates for each account are located and printed.
In the second look-up specification, the Invoice No (account.dat) is matched with the Invoice No (in invoice.dat). Then the invoice line items for each invoice number are printed on the report.
Since each one-to-many specification automatically creates a level break, two level breaks are effected by the above look-up. The first level break is at the customer number, and the second level break is at the invoice number.
The invoice dates and invoice line items are the fields that will print per the above specification (fields on right side of symbol). There will be two nested levels, with all of the invoice line items printing a level below the invoice dates. It is recommended that you also specify the customer no and invoice number separately to show a full report. The report may appear as follows:
1st Level Break 2nd Level Break
Customer No Invoice Date Invoice Number Line Item
1000-000 01/01/99 000000100 Description
Description
-----------
Sub-total 1
02/04/99 000000200 Description
Description
Description
-----------
Sub-total 1
03/01/99 000000300 Description
Description
Description
-----------
Sub-total 1
Sub-total 2
2000-000 01/05/99 000000400 Description
Description
-----------
Sub-total 1
01/06/99 000000500 Description
Description-----------
Sub-total 1
Sub-total 2
If Field 1 and Field 3 are in the same file, the lookup would be performed as follows. (Note that Fields 2 and 4 can be in different files.)
File 1 GENERAL LEDGER File 2 TRANSACTIONS
First lookup: (account no, "1") +> transaction amount
(Account No plus the Period match the key field of the Transactions File.)
The report for the preceding look-up specification would appear in the following format (if sub-totals and totals are also specified).
GL Acct No Period Amount
1200000 1 Amount
Amount
Amount
Amount
Sub-total 1 (All period 1 amounts for 1200000)
2 Amount
Amount
Amount
Sub-total 1 (All period 2 amounts for 1200000)
Sub-total 2 (All amounts for 1200000)
1300000 1 Amount
Amount
Amount
Sub-total 1 (All period 1 amounts for 1300000)
2 Amount
Amount
Sub-total 1 (All period 2 amounts for 1300000)
Sub-total 2 (All amounts for 1300000)
* Section *
18.3.5 Sub-Totalling with One-to-Many Lookups
The field specified at the one-to-many look-up specification is automatically assigned a sub-total level. (Note, however, that you do not indicate Y to Start of Level Break for that field.) Be sure to consider this when specifying subtotals.
Following are example report design selections:
Detail line:
Choose fields Salesman Name
no level break set
Detail line:
Choose fields
field from another file
(choose field) Salesman No +> Commissions
Document No
no level break set
Subtotal line 1:
Choose fields commissions document no
calculated field count, set on first subtotal level
The above specifications would give you a report in the following general format:
Salesman Name Doc-No
John Adams 04678
05980
09900
3
Roger Smith 03579
06900
2
For more information on assigning sub-total levels, refer to Chapter 13 of this User's Guide.
There are some special cases which relate to one-to-many look-ups which are outlined on the next page.
Example: Field 1 +> Field 2, ... Field 2 +> Field 3
